package com.dinghan.qccproject.service.impl;

import cn.hutool.core.date.DateUtil;
import com.dinghan.qccproject.pojo.QccInfo;
import com.dinghan.qccproject.service.ExcelService;
import com.dinghan.qccproject.service.QccInfoService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    QccInfoService qccInfoService;

    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    @Override
    public List<QccInfo> getNotExistCell(InputStream in, String fileName) throws Exception {
        List<QccInfo> list = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            for (int j = sheet.getFirstRowNum() + 2; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                String tel = row.getCell(9).getStringCellValue();
                Integer isExist = qccInfoService.selectOneByTel(tel);
                if (isExist != null && isExist > 0) {
                    continue;
                }
                QccInfo info = new QccInfo();
                info.setCompName(row.getCell(0).getStringCellValue());
                info.setCompUser(row.getCell(2).getStringCellValue());
                info.setCompMoney(row.getCell(3).getStringCellValue());
                info.setStartTime(DateUtil.parse(row.getCell(4).getStringCellValue()));
                info.setProvince(row.getCell(6).getStringCellValue());
                info.setCity(row.getCell(7).getStringCellValue());
                info.setArea(row.getCell(8).getStringCellValue());
                info.setTel(row.getCell(9).getStringCellValue());
                info.setTelMore(row.getCell(10).getStringCellValue());
                info.setEmail(row.getCell(11).getStringCellValue());
                info.setEmailMore(row.getCell(12).getStringCellValue());
                info.setCreditCode(row.getCell(13).getStringCellValue());
                info.setIdNum(row.getCell(14).getStringCellValue());
                info.setOrgCode(row.getCell(16).getStringCellValue());
                info.setCompType(row.getCell(18).getStringCellValue());
                info.setIndustry(row.getCell(19).getStringCellValue());
                info.setCompAddr(row.getCell(23).getStringCellValue());
                info.setCreateTime(DateUtil.date());
                qccInfoService.save(info);
                list.add(info);
            }
        }
        work.close();
        return list;
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    private Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }

    /**
     * 用户信息导出类
     *
     * @param response   响应
     * @param fileName   文件名
     * @param columnList 每列的标题名
     * @param dataList   导出的数据
     */
    @Override
    public void uploadExcelAboutUser(HttpServletResponse response, String fileName, List<String> columnList, List<String> dataList) {
        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(response, fileName);
        try {
            //获取输出流
            os = response.getOutputStream();
            //内存中保留1000条数据，以免内存溢出，其余写入硬盘
            SXSSFWorkbook wb = new SXSSFWorkbook(10000);
            //获取该工作区的第一个sheet
            Sheet sheet1 = wb.createSheet("sheet1");
            int excelRow = 0;
            //创建标题行
//            Row titleRow = sheet1.createRow(excelRow++);
//            for(int i = 0;i<columnList.size();i++){
//                //创建该行下的每一列，并写入标题数据
//                Cell cell = titleRow.createCell(i);
//                cell.setCellValue(columnList.get(i));
//            }
            //设置内容行
            if (dataList != null && dataList.size() > 0) {
                //序号是从1开始的
                int count = 1;
                //外层for循环创建行
                for (int i = 0; i < dataList.size(); i++) {
                    Row dataRow = sheet1.createRow(excelRow++);
                    Cell cell = dataRow.createCell(0);
                    cell.setCellValue(dataList.get(i));
                    //内层for循环创建每行对应的列，并赋值
//                    for (int j = -1; j < dataList.get(0).size(); j++) {//由于多了一列序号列所以内层循环从-1开始
//                        Cell cell = dataRow.createCell(j + 1);
//                        if (j == -1) {//第一列是序号列，不是在数据库中读取的数据，因此手动递增赋值
//                            cell.setCellValue(count++);
//                        } else {//其余列是数据列，将数据库中读取到的数据依次赋值
//                            cell.setCellValue(dataList.get(i).get(j));
//                        }
//                    }
                }
            }
            //将整理好的excel数据写入流中
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭输出流
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
        设置浏览器下载响应头
     */
    private void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
